package mudbconnection;

import java.util.List;

import javax.sql.DataSource;

import mudbconnection.DBSchoolsService.SchoolMapper;
import mumethod.Base64Object;
import mumethod.LibFunction;
import muobject.School;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

@Component("DBScoresService")
public class DBScoresService implements DBScores {

	private JdbcTemplate jdbct;
	private Base64Object base64Object = new LibFunction();
	
	@Autowired
	public DBScoresService(DataSource jdbct) {
		super();
		this.jdbct = new JdbcTemplate(jdbct);
	}
	
	public int getHWScore(String sex, int age, double height, double weight){
		int heightid;
		int score;
		try {
			if(height==0 | weight==0){
				return 0;
			}else{
				if(sex.equals("M")){
					heightid = jdbct.queryForInt("Select Bhid from Boyhight where (Agelower <= ? and Ageupper >=?) and " +
						" (Htlower <= ? and Htupper >= ?) ", new Object[]{ age, age, height, height });
					score = jdbct.queryForInt("Select Score from Boyweigth where Bhid = ? and (Wgtlower <= ? and Wgtupper >= ?) ", 
							new Object[]{ heightid, weight, weight });
				}else{
					heightid = jdbct.queryForInt("Select Ghid from Grilhight where (Agelower <= ? and Ageupper >=?) and " +
							" (Htlower <= ? and Htupper >= ?) ", new Object[]{ age, age, height, height });
					score = jdbct.queryForInt("Select Score from Grilweight where Ghid = ? and (Wgtlower <= ? and Wgtupper >= ?) ", 
							new Object[]{ heightid, weight, weight });
				}
				return score;
			}
		} catch (Exception e) {
			System.out.println(e.getMessage());
			return 0;
		}
	}
	
	public int getPhysicalScore(String sex, int age, String item, double data){
		int score;
		try {
			if(data <= 0){
				return 0;
			}else{
				score = jdbct.queryForInt("Select Score from Physicalscore where Sex = ? and Age = ? And Item = ? And " +
						"(Nlower <= ? and Nupper >= ?) ", 
							new Object[]{ sex, age, item, data, data });
				return score;
			}
		} catch (Exception e) {
			System.out.println(e.getMessage());
			return 0;
		}
	}
	
	public String getAdviceScore(String advice, String type, int no){
		String score;
		try {
			if(advice.equals("advice"))
				score = (String)jdbct.queryForObject("Select advice from AdviceScore where testgroup = ? and score = ? ", 
						new Object[]{ type, no }, String.class);
			else
				score = (String)jdbct.queryForObject("Select dosport from AdviceScore where testgroup = ? and score = ? ", 
						new Object[]{ type, no }, String.class);
			return score;
		} catch (Exception e) {
			System.out.println(e.getMessage());
			return "";
		}
	}
	
}
